﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using NTQOnlineExam.Services.Infrastructure;

namespace NTQOnlineExam.Services.CommandService.Question
{
    public class ReArrangeQuestionOrderDbCommand : ICommand<bool>
    {
        private readonly int _termId;
        private readonly int _sectionId;
        private const string SQL = @"
	        UPDATE	f
	            SET		f.[Order] = c.ReOrder		
	            FROM	dbo.Question f INNER JOIN 
						            (SELECT QuestionId, row_number() over (ORDER BY [Order]) as ReOrder
						             FROM	dbo.Question
						            Where	TermId = @TermId AND SectionId = @SectionId) AS c ON f.QuestionId = c.QuestionId
	            WHERE	f.TermId = @TermId AND SectionId = @SectionId
	
        ";
        public ReArrangeQuestionOrderDbCommand(int termId, int sectionId)
        {
            _termId = termId;
            _sectionId = sectionId;
        }

        public bool Execute()
        {
            var connectionString = ConfigurationSettings.AppSettings["ConnectionString"];

            using (var con = new SqlConnection(connectionString))
            {
                using (var cmd = new SqlCommand(SQL, con))
                {
                    cmd.CommandType = CommandType.Text;
                    cmd.Parameters.Add("@TermId", SqlDbType.Int).Value = _termId;
                    cmd.Parameters.Add("@SectionId", SqlDbType.Int).Value = _sectionId;
                    con.Open();
                    cmd.ExecuteNonQuery();
                }
            }
            return true;

        }
    }
}
